//
//  DataHandle.m
//  Menu
//
//  Created by dllo on 15/7/2.
//  Copyright (c) 2015年 lijiangang. All rights reserved.
//

#import "DataHandle.h"

@implementation DataHandle

+(instancetype)shareData
{
    static DataHandle *dataOnly = nil;
    if (dataOnly == nil)
    {
        dataOnly = [[DataHandle alloc] init];
    }
    return dataOnly;
}

//导入数据库类 libsqlite3.0
//引入头文件 创建数据库对象
static sqlite3 *dataBase = nil;

//打开数据库
-(void)openDataBase
{
    if (dataBase != nil)
    {
        return;//如果数据库对象存在,则不往下执行.
    }
    
    //创建数据库文件路径
    NSArray *dataArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [dataArray lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"dataBase.sqlite"];
    NSLog(@"%@",sqlitePath);
    //打开数据库函数,将数据库对象与数据库文件关联,并打开数据库.
    int result = sqlite3_open(sqlitePath.UTF8String, &dataBase);
    if (result == SQLITE_OK)
    {
//        NSLog(@"打开数据库成功");
    }
    else
    {
        NSLog(@"打开数据库失败");
    }
}

//创建视频信息表
-(void)createVideoInfoTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS videoInfo (number INTEGER PRIMARY KEY AUTOINCREMENT, videoTitle TEXT, videoType TEXT, imgURL TEXT, playURL TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"创建视频信息表成功");
    }
    else
    {
        NSLog(@"创建视频信息表失败");
    }
}

//插入视频信息
-(void)insertVideoInfo:(VideoModel *)theVideo
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO videoInfo (videoTitle,videoType,imgURL,playURL) VALUES('%@', '%@', '%@', '%@')",theVideo.title,theVideo.category,theVideo.previewImage,theVideo.link];
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"插入视频信息成功");
    }
    else
    {
        NSLog(@"插入视频信息失败");
    }
}

//查找视频信息表
-(NSMutableArray *)selectVideoInfoTable
{
    NSString *sql = @"SELECT * FROM videoInfo";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare16_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectArray = [NSMutableArray array];
    if (result == SQLITE_OK)
    {
//        NSLog(@"准备查询视频信息操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //取出表中每行各个字段中的数据
            const unsigned char *videoTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *videoType = sqlite3_column_text(stmt, 2);
            const unsigned char *imgURL = sqlite3_column_text(stmt, 3);
            const unsigned char *playURL = sqlite3_column_text(stmt, 4);
            
            VideoModel *videoSelected = [[VideoModel alloc] init];
            videoSelected.title = [NSString stringWithUTF8String:(const char *)videoTitle];
            videoSelected.category = [NSString stringWithUTF8String:(const char *)videoType];
            videoSelected.previewImage = [NSString stringWithUTF8String:(const char *)imgURL];
            videoSelected.link = [NSString stringWithUTF8String:(const char *)playURL];
            [selectArray addObject:videoSelected];
        }
        return selectArray;
    }
    else
    {
        NSLog(@"准备查询视频信息操作失败");
        return selectArray;
    }
}


//创建搜索历史信息表
-(void)createSearchHistoryTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS searchHistory (number INTEGER PRIMARY KEY AUTOINCREMENT, searchText TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"创建搜索历史信息表成功");
    }
    else
    {
        NSLog(@"创建搜索历史信息表失败");
    }
}

//插入搜索信息
-(void)insertSearchInfoToHistoryTable:(NSString *)searchText
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO searchHistory (searchText) VALUES('%@')",searchText];
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"插入搜索信息成功");
    }
    else
    {
        NSLog(@"插入搜索信息失败");
    }
}
//---------------------收藏表-------------
//创建收藏表
-(void)createCollectInfoTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS CollectTable (number INTEGER PRIMARY KEY AUTOINCREMENT, foodName TEXT, typeID TEXT, picUrl TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"创建收藏信息表成功");
    }
    else
    {
        NSLog(@"创建收藏信息表失败");
    }
}
//插入收藏信息
-(void)insertCollectInfo:(CollectModel *)theCollect
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO CollectTable (foodName,typeID,picUrl) VALUES('%@', '%@', '%@')",theCollect.foodName,theCollect.typeID,theCollect.picUrl];
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"插入收藏信息成功");
    }
    else
    {
        NSLog(@"插入视频信息失败");
    }
}
//查找收藏信息表
-(NSMutableArray *)selectCollectInfoTabel
{
    NSString *sql = @"SELECT * FROM CollectTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *collectArray = [NSMutableArray array];
    if (result == SQLITE_OK)
    {
//        NSLog(@"准备查询收藏信息操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //取出表中每行各个字段中的数据
            const unsigned char *foodName = sqlite3_column_text(stmt, 1);
            const unsigned char *typeID = sqlite3_column_text(stmt, 2);
            const unsigned char *picUrl = sqlite3_column_text(stmt, 3);
            CollectModel *collectModel = [[CollectModel alloc] init];
            collectModel.foodName = [NSString stringWithUTF8String:(const char *)foodName];
            collectModel.typeID = [NSString stringWithUTF8String:(const char *)typeID];
            collectModel.picUrl = [NSString stringWithUTF8String:(const char *)picUrl];
            [collectArray addObject:collectModel];
        }
        return collectArray;
    }
    else
    {
        NSLog(@"准备查询收藏信息操作失败");
        return collectArray;
    }
}
//删除Model
-(void)deleteCollectTable:(NSString *)foodName
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM CollectTable WHERE foodName = '%@'",foodName];
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除收藏信息表成功");
    }else{
        NSLog(@"删除收藏信息表失败");
    }
    
}
//删除收藏表
-(void)dropTable
{
    NSString *sql = @"DROP TABLE CollectTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL,nil );
    if (result == SQLITE_OK) {
//        NSLog(@"删除收藏表成功");
    }else{
        NSLog(@"删除收藏表失败");

    }
}

//查找搜索信息表
-(NSMutableArray *)selectSearchHistoryTable
{
    NSString *sql = @"SELECT * FROM searchHistory";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *searchHistoryArray = [NSMutableArray array];
    if (result == SQLITE_OK)
    {
//        NSLog(@"准备查找搜索信息操作成功");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            const unsigned char *searched = sqlite3_column_text(stmt, 1);
            NSString *searchStr = [NSString stringWithUTF8String:(const char *)searched];
            [searchHistoryArray insertObject:searchStr atIndex:0];
        }
        return searchHistoryArray;
    }
    else
    {
        NSLog(@"准备查找搜索信息操作失败");
        return searchHistoryArray;
    }
}

-(void)dropSearchHistoryTable
{
    NSString *sql = @"DROP TABLE searchHistory";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"删除搜索信息表成功");
    }
    else
    {
        NSLog(@"删除搜索信息表失败");
    }
}


-(void)deleteOneOfSearch:(NSString *)searchText
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM searchHistory WHERE searchText = '%@'",searchText];
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK)
    {
//        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败");
    }
}

//-----------首页一级缓存------
//创建
-(void)createHomeCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS homeCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, homeTitle TEXT, homeId TEXT, homeImageUrl TEXT, homeSmallImageUrl TEXT)";
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建首页表成功");
    } else {
        NSLog(@"创建首页表失败");
    }
    
}
//插入
-(void)insertHomeCache:(HomePageModel *)homeModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO homeCacheTable (homeTitle, homeId, homeImageUrl, homeSmallImageUrl) VALUES ('%@', '%@', '%@', '%@')", homeModel.name, homeModel.vegetable_id, homeModel.imagePathLandscape, homeModel.imagePathThumbnails];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入首页成功");
    } else {
        
        NSLog(@"插入首页失败");
    }
}
//查找
-(NSMutableArray *)selectHomeCacheTable{
    
    NSString *sql = @"SELECT * FROM homeCacheTable";
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectArray = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询首页信息成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *homeTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *homeId = sqlite3_column_text(stmt, 2);
            const unsigned char *homeImageUrl = sqlite3_column_text(stmt, 3);
            const unsigned char *homeSmallImageUrl = sqlite3_column_text(stmt, 4);
            
            HomePageModel *homeModel = [[HomePageModel alloc] init];
            homeModel.name = [NSString stringWithUTF8String:(const char *)homeTitle];
            homeModel.vegetable_id = [NSString stringWithUTF8String:(const char *)homeId];
            homeModel.imagePathLandscape = [NSString stringWithUTF8String:(const char *)homeImageUrl];
            homeModel.imagePathThumbnails = [NSString stringWithUTF8String:(const char *)homeSmallImageUrl];
            [selectArray addObject:homeModel];
        }
        return selectArray;
    } else {
        
        NSLog(@"准备查询首页失败");
        return nil;
    }
    
}
//删除
-(void)dropHomeCacheTable{
    
    NSString *sql = @"DROP TABLE homeCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除主页缓存成功");
    } else {
        
        NSLog(@"删除主页缓存失败");
    }
}

//-----------------精品汇一级缓存-------------
//创建
-(void)createGoodCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS goodCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, goodTitle TEXT, goodId TEXT, goodImageUrl TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建精品汇信息表成功");
    } else {
        
        NSLog(@"创建精品汇信息表失败");
    }
}
//插入
-(void)insertGoodCacheTable:(GoodModel *)goodModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO goodCacheTable (goodTitle, goodId, goodImageUrl) VALUES ('%@', '%@', '%@')",goodModel.name, goodModel.typeId,goodModel.imageFilename];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入收藏信息成功");
    } else{
        
        NSLog(@"插入收藏信息失败");
    }
}
//查找
-(NSMutableArray *)selectGoodCacheTable{
    
    NSString *sql = @"SELECT * FROM goodCacheTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectGoodArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询精品汇操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *goodTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *goodId = sqlite3_column_text(stmt, 2);
            const unsigned char *goodImageUrl = sqlite3_column_text(stmt, 3);
            
            GoodModel *good = [[GoodModel alloc] init];
            good.name = [NSString stringWithUTF8String:(const char *)goodTitle];
            good.typeId = [NSString stringWithUTF8String:(const char *)goodId];
            good.imageFilename = [NSString stringWithUTF8String:(const char *)goodImageUrl];
            [selectGoodArray addObject:good];
        }
        return selectGoodArray;
    } else {
        
        NSLog(@"准备查询精品汇操作失败");
        return  nil;
    }
}
//删除
-(void)dropGoodCacheTable{
    
    NSString *sql = @"DROP TABLE goodCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"删除精品汇表成功");
    }else{
        NSLog(@"删除精品汇表失败");
        
    }
}

//-----------------新鲜事一级缓存-------------
//创建
-(void)createNewsCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS newsCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, newsTitle TEXT, newscontent TEXT, newsImageUrl TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建新鲜事信息表成功");
    } else {
        
        NSLog(@"创建新鲜事信息表失败");
    }
}
//插入
-(void)insertNewsCacheTable:(NewsListModel *)newsListModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO newsCacheTable (newsTitle, newscontent, newsImageUrl) VALUES ('%@', '%@', '%@')",newsListModel.name, newsListModel.content, newsListModel.titleImageFile];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入新鲜事信息成功");
    } else{
        
        NSLog(@"插入新鲜事信息失败");
    }
}
//查找
-(NSMutableArray *)selectNewsCacheTable{
    
    NSString *sql = @"SELECT * FROM newsCacheTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectGoodArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询新鲜事操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *newsTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *newsContent = sqlite3_column_text(stmt, 2);
            const unsigned char *newsImageUrl = sqlite3_column_text(stmt, 3);
            
            NewsListModel *news = [[NewsListModel alloc] init];
            news.name = [NSString stringWithUTF8String:(const char *)newsTitle];
            news.content = [NSString stringWithUTF8String:(const char *)newsContent];
            news.titleImageFile = [NSString stringWithUTF8String:(const char *)newsImageUrl];
            [selectGoodArray addObject:news];
        }
        return selectGoodArray;
    } else {
        
        NSLog(@"准备查询新鲜事操作失败");
        return  nil;
    }
}
//删除
-(void)dropNewsCacheTable{
    
    NSString *sql = @"DROP TABLE newsCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除新鲜事表成功");
    }else{
        
        NSLog(@"删除新鲜事表失败");
        
    }
}

//-----------------粥汤TableView一级缓存-------------
//创建
-(void)createCaudleListCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS caudleListCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, caudleListKinds TEXT, caudleListId TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建粥汤信息表成功");
    } else {
        
        NSLog(@"创建粥汤信息表失败");
    }
}
//插入
-(void)insertCaudleListCacheTable:(CaudleModel *)caudleModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO caudleListCacheTable (caudleListKinds, caudleListId) VALUES ('%@', '%@')", caudleModel.caudleKinds, caudleModel.typeId];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入粥汤信息成功");
    } else{
        
        NSLog(@"插入粥汤信息失败");
    }
}
//查找
-(NSMutableArray *)selectCaudleListCacheTable{
    
    NSString *sql = @"SELECT * FROM caudleListCacheTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectCaudleListArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询粥汤操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            const unsigned char *caudleListKinds = sqlite3_column_text(stmt, 1);
            const unsigned char *caudleListId = sqlite3_column_text(stmt, 2);
            
            CaudleModel *caudle = [[CaudleModel alloc] init];
            
            caudle.caudleKinds = [NSString stringWithUTF8String:(const char *)caudleListKinds];
            caudle.typeId = [NSString stringWithUTF8String:(const char *)caudleListId];

            [selectCaudleListArray addObject:caudle];
        }
        return selectCaudleListArray;
    } else {
        
        NSLog(@"准备查询粥汤操作失败");
        return  selectCaudleListArray;
    }
}
//删除
-(void)dropCaudleListCacheTable{
    
    NSString *sql = @"DROP TABLE caudleListCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除粥汤表成功");
    }else{
        
        NSLog(@"删除粥汤表失败");
        
    }
}

//-----------------粥汤CollectionView一级缓存-------------
//创建
-(void)createCaudleCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS caudleCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, caudleTitle TEXT, caudleImageUrl TEXT, caudleListId TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建粥汤信息表成功");
    } else {
        
        NSLog(@"创建粥汤信息表失败");
    }
}
//插入
-(void)insertCaudleCacheTable:(CaudleModel *)caudleModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO caudleCacheTable (caudleTitle, caudleImageUrl, caudleListId) VALUES ('%@', '%@', '%@')", caudleModel.name, caudleModel.imageFilename, caudleModel.typeId];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入粥汤信息成功");
    } else{
        
        NSLog(@"插入粥汤信息失败");
    }
}
//查找全部
-(NSMutableArray *)selectCaudleCacheTable{
    
    NSString *sql = @"SELECT * FROM caudleCacheTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectCaudleArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询粥汤操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            const unsigned char *caudleTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *caudleImageUrl = sqlite3_column_text(stmt, 2);
            const unsigned char *caudleListId = sqlite3_column_text(stmt, 3);
            
            CaudleModel *caudle = [[CaudleModel alloc] init];
            caudle.name = [NSString stringWithUTF8String:(const char *)caudleTitle];
            caudle.imageFilename = [NSString stringWithUTF8String:(const char *)caudleImageUrl];
            caudle.typeId = [NSString stringWithUTF8String:(const char *)caudleListId];
            [selectCaudleArray addObject:caudle];
        }
        return selectCaudleArray;
    } else {
        
        NSLog(@"准备查询粥汤操作失败");
        return  selectCaudleArray;
    }
}

//根据typeId查找caudle
-(NSMutableArray *)selectCaudleCacheTable:(NSString *)typeId
{
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM caudleCacheTable WHERE caudleListId = '%@'", typeId];
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectCaudleArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询粥汤操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            const unsigned char *caudleTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *caudleImageUrl = sqlite3_column_text(stmt, 2);
            const unsigned char *caudleListId = sqlite3_column_text(stmt, 3);
            
            CaudleModel *caudle = [[CaudleModel alloc] init];
            caudle.name = [NSString stringWithUTF8String:(const char *)caudleTitle];
            caudle.imageFilename = [NSString stringWithUTF8String:(const char *)caudleImageUrl];
            caudle.typeId = [NSString stringWithUTF8String:(const char *)caudleListId];
            [selectCaudleArray addObject:caudle];
        }
        return selectCaudleArray;
    } else {
        
        NSLog(@"准备查询粥汤操作失败");
        return  selectCaudleArray;
    }
    
    
    
}

//删除
-(void)dropCaudleCacheTable{
    
    NSString *sql = @"DROP TABLE caudleCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除粥汤表成功");
    }else{
        
        NSLog(@"删除粥汤表失败");
    }
}
//删除特定字段
-(void)deleteCaudleCacheTable:(NSString *)typeId{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM caudleCacheTable WHERE caudleListId = '%@'", typeId];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除typeId相关字段成功");
    } else {
        
        NSLog(@"删除typeId相关字段失败");
    }
}

//-----------------视频教程TableView一级缓存-------------
//创建
-(void)createVideoCacheTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS videoCacheTable (number INTEGER PRIMARY KEY AUTOINCREMENT, videoTitle TEXT, videoImage TEXT, videoUrl TEXT, videoType TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建视频教程表成功");
    } else {
        
        NSLog(@"创建视频教程表失败");
    }
}
//插入
-(void)insertVideoCacheTable:(VideoModel *)videoModel{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO videoCacheTable (videoTitle, videoImage, videoUrl, videoType) VALUES ('%@', '%@', '%@', '%@')", videoModel.title, videoModel.previewImage, videoModel.link,videoModel.category];
    
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"插入视频教程成功");
    } else{
        
        NSLog(@"插入视频教程失败");
    }
}
//查找
-(NSMutableArray *)selectVideoCacheTable{
    
    NSString *sql = @"SELECT * FROM videoCacheTable";
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectVideoArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备查询视频教程成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *videoTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *videoImage = sqlite3_column_text(stmt, 2);
            const unsigned char *videoUrl = sqlite3_column_text(stmt, 3);
            const unsigned char *videoType = sqlite3_column_text(stmt, 4);
            
            VideoModel *video= [[VideoModel alloc] init];
            
            video.title = [NSString stringWithUTF8String:(const char *)videoTitle];
            video.previewImage = [NSString stringWithUTF8String:(const char *)videoImage];
            video.link = [NSString stringWithUTF8String:(const char *)videoUrl];
            video.category = [NSString stringWithUTF8String:(const char *)videoType];
            
            [selectVideoArray addObject:video];
        }
        return selectVideoArray;
    } else {
        
        NSLog(@"准备视频教程操作失败");
        return  selectVideoArray;
    }
}
//按type查找视频
-(NSMutableArray *)selectVideoCacheTable:(NSString *)type{
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM videoCacheTable WHERE videoType = '%@'",type];
    
    //创建数据库跟随指针对象
    sqlite3_stmt *stmt = nil;
    //进行查询准备
    int result = sqlite3_prepare_v2(dataBase, sql.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectVideoArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
//        NSLog(@"准备视频教程操作成功");
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *videoTitle = sqlite3_column_text(stmt, 1);
            const unsigned char *videoImage = sqlite3_column_text(stmt, 2);
            const unsigned char *videoUrl = sqlite3_column_text(stmt, 3);
            const unsigned char *videoType = sqlite3_column_text(stmt, 4);
            
            VideoModel *video= [[VideoModel alloc] init];
            
            video.title = [NSString stringWithUTF8String:(const char *)videoTitle];
            video.previewImage = [NSString stringWithUTF8String:(const char *)videoImage];
            video.link = [NSString stringWithUTF8String:(const char *)videoUrl];
            video.category = [NSString stringWithUTF8String:(const char *)videoType];
            
            [selectVideoArray addObject:video];
        }
        return selectVideoArray;
    } else {
        
        NSLog(@"准备视频教程操作失败");
        return  selectVideoArray;
    }
}
//删除
-(void)dropVideoCacheTable{
    
    NSString *sql = @"DROP TABLE videoCacheTable";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
//        NSLog(@"删除视频教程表成功");
    }else{
        
        NSLog(@"删除视频教程表失败");
        
    }
}
-(void)createRandomTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS randomTable (number INTEGER PRIMARY KEY AUTOINCREMENT, goodTitle TEXT, goodId TEXT, goodImageUrl TEXT)";
    int result = sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
        //        NSLog(@"创建精品汇信息表成功");
    } else {
        
        NSLog(@"创建精品汇信息表失败");
    }
}

@end








